面试必须要知道的MySQL知识（上）

欢迎点击关注公众号，利用碎片化时间学习，每天进步一点点。

来源：https://blog.csdn.net/a18602320276/article

/details/122917218

本文跟你来聊聊MySQL一些底层原理知识，由于文章内容较多，所以拆分为好几篇文章，欢迎大家关注公众号，及时获得下文。

1 写在前面的话

想要更好地阅读本文，您可能需要自行安装 MySQL，并熟练掌握 MySQL 的基本语法和使用。

2 MySQL 架构设计

2.1 程序是如何跟 MySQL 打交道的

MySQL 作为标准的 C/S 架构，分为客户端和服务端。我们写代码的时候，通常会在代码中引入驱动和 client，然后在配置文件中填写 server 地址和账号密码，用于连接到 MySQL 服务器。大概的流程看起来就向下面一样。

2.2 程序是如何跟 MySQL 打交道的图解

2.3 服务端流程分析

客户端向服务端发送请求并得到回复的过程本质上是一个进程间通信的过程，这个处理连接的过程，MySQL 支持以下 3 种方式：

• TCP/IP（端口：3306）；

• 命名管道和共享内存（这个针对于 windows 系统）；

• Unix 域套接字文件（这个针对于 linux 系统）。

处理连接后，MySQL 会对我们发送的请求进行解析与优化，这个过程大概分为 3 步：

• 查询缓存；

• 语法解析；

• 查询优化。

  然后再经过存储引擎，最后持久化。

2.4 服务端流程图解

为了方便大家记忆这个过程，画了下面一张图

以上内容，作为一个 CRUD 工程师，不需要掌握得那么深，了解即可。下文将会讲述重点知识 InnoDB 存储引擎。

3 InnoDB 架构设计

3.1 设计思路

如果让你来设计一个数据库，你会怎么做？结合我们平常的对 MySQL 的使用，我觉得我们至少需要实现以下几个功能：

• 数据需要持久化；

• 支持的并发不能太低，速度要可以；

• 一旦宕机，需要尽可能的减少数据的丢失，能够快速恢复数据；

• 如果某一操作有问题，应该可以快速回滚。

实现如下

• 针对第一点，咱们可以将数据写入磁盘中（MySQL 的磁盘文件）；

• 针对第二点，咱们可以考虑先基于内存处理，然后再写入磁盘（MySQL 是通过 Buffer Pool 缓冲池实现的）；

• 针对第三点，咱们可以记录一下当前的操作记录，类比与 redis 的 AOF 文件（MySQL 中叫 redo log）；

• 针对第四点，咱们可以设计一个文件，专门存放每条操作记录更新前的值（MySQL 中叫 undo log）。

接下来，我们借助一个更新语句，来看看 InnoDB 存储引擎的架构设计。

首先我们从磁盘文件中读取数据，在更新内存数据之前，将旧数据写入 undo log，同时写入 redo log，整个流程如下（其中的 OS cache 和 Redo Log Buffer，读者可以将其看做缓存，后面有涉及，将会详细讲解）：

3.2 图解

4 MySQL 物理数据模型

4.1 数据在磁盘上的存储格式

我们每一行数据在磁盘上到底是怎么存储的呢？我们以常见的varchar为例，他的存储格式大概如下图所示：

注意：变长字段长度实际上是倒序存储的。

4.2 null列表与数据头

下图展示了，null列表与数据头的详细信息，了解即可。

4.3 行溢出

什么叫行溢出？就是说一行数据太多了，多的一个数据页都放不下了，需要放在其他数据页里面（这些数据页是由链表串联起来的），这个就叫行溢出。（数据页的详细介绍，请关注继续关注本公众号，后面会有详解）

5 BufferPool

首先我们通过下图，简单地了解一下 BufferPool 的内存数据结构

我们知道 MySQL 的数据最后都是存放在磁盘文件中的，MySQL 将这一行行数据，放入到了一个一个的叫数据页的数据结构中，然后数据页会被 MySQL 加载到 BufferPool 中。

BufferPool 主要由描述数据和缓存页构成，默认情况下每一个数据页对应一个缓存页，每一个缓存页都有一个对应的描述数据，描述数据你可以将它看做是缓存页的概览，我们可以通过描述数据找到与之对应的缓存页。

5.1 free 链表

5.1.1 概念

在 MySQL 服务端启动的时候，MySQL 会在内存中开辟一块 BufferPool，并初始化好对应的描述数据以及缓存页。这个时候缓存页都是空的。

然后当我们进行增删改操作的时候，BufferPool 才会将数据对应的数据页读出来，放在缓存页中。这个时候，就出现一个问题了，我们怎么知道哪些缓存页是空的呢？MySQL 为我们引入了另外一个概念，free 链表。他是一个双向链表数据结构，每一个节点都存放了空置的描述数据的地址，并且他还有一个基础节点，存放的是控制缓存页的个数。

5.1.2 缓存页 hash 表

现在我们通过 free 链表，知道了哪些缓存页是空的，但是我们并不知道哪些数据页是被缓存了的呢？其实 MySQL 中有一个缓存页 hash 表，如果在此表中，则表明数据已经被缓存了，他的 key=表空间号+数据页号，他的 value=缓存页地址。

(表空间号+数据页号,缓存页地址)

5.1.3 图解

5.2 flush 链表

5.2.1 概念

如果你在执行增删改的时候，发现数据页没有被缓存，那么 MySQL 就会通过 free 链表找到对应的描述数据，最后缓存到缓存页中，并且断开 free 链表中对应的描述数据节点。但是这又会引出另外一个问题，只要你改变了缓存页的数据，那么缓存页肯定就和磁盘上的数据页不一致了，这个时候需要将缓存页的数据刷到磁盘上去，那么刷哪些数据呢，总不能全量刷盘吧？于是 MySQL 引入了另外一个链表 flush 链表。他的数据结构和 free 链表一致，只不过，他的节点放置的是需要被刷回磁盘的描述数据地址。

5.2.2 图解

5.3 LRU 链表

5.3.1 概念

从前面的文章中我们知道了 BufferPool 中存在缓存页，但是我们思考一下，缓存页是启动的时候就分配好了的，如果满了怎么办？要么扩容，要么淘汰。MySQL 使用的是 LRU 算法淘汰部分缓存。而这个 LRU 算法，是基于 LRU 链表的。最近被访问过的缓存页，会被挪到链表最前面，因此最少访问的缓存页就会在链表的最尾部，淘汰缓存时，我们只需要淘汰最后的数据页即可。

5.3.2 图解

5.3.3 LRU 链表存在的问题

1）预读机制对 LRU 链表的影响

为了提高效率，MySQL 从磁盘上加载数据到缓存的时候，他可能会把数据页相邻的其他数据页也加载到缓存中去，这个就是 MySQL 的预读机制。

我们思考一下这样的预读机制会对 LRU 链表造成什么影响呢？

这些被查出来的预读数据，可能根本不常用，但是他还是被放在了 LRU 链表的前面，从而导致他们不能被及时淘汰。

2）触发预读机制的常见情况

• innodb_read_ahead_threshold

  默认 56，如果顺序访问一个区里的多个数据页的数量超过了这个阀值，那么就会把相邻区中所有的数据页都加载到缓存里去。

• innodb_random_read_ahead

  默认 off，如果 Buffer Pool 里缓存了一个区 13 个连续的数据页，且这些数据页会被频繁访问，那么就会把这个区的其他数据页加载到缓存里去。

3）全表扫描对 LRU 链表的影响

• select * from table

  全表扫描，会将表里所有的数据页都从磁盘加载到 Buffer Pool 里面去，导致 LRU 尾部的链表反而是频繁被访问的数据。

4）图解

5.3.4 MySQL 对 LRU 算法的优化

通过上面的分析，我们知道了 LRU 算法可能会存在的一些问题，写 MySQL 的大佬们当然也想到了这些问题，下文列举了 MySQL 对 LRU 算法的两种优化。

1）通过冷热数据分离，优化 LRU 算法

前面的问题为什么会出现呢？很大原因是因为所有数据都放在 LRU 链表中，如果我们把他分成冷热数据两部分，预读数据、全表扫描和其他不常用的数据放在冷数据区，其他常用的放在热数据区，缓存淘汰的时候，只淘汰冷数据区的数据，是不是就解决这个问题了？这个思想跟秒杀系统中热数据放 redis，冷数据放数据库，个人感觉也是异曲同工。

以下是相关的两个参数，了解即可，一般不会去修改他。

• innodb_old_blocks_time
  设置冷数据豁免时间，默认 1000ms。（1 秒内，被访问，则不会转移到热数据区域）

• innodb_old_blocks_pct
  设置冷数据区域所占大小，默认 37%

2）通过定时任务，优化 LRU 算法

为了提升效率，MySQL 开启一个后台线程，定时把冷数据尾部的一些数据输入磁盘。

5.4 free 链表、flush 链表、LRU 链表，修改数据的动态联系

本文完。由于篇幅有限，下文我会另起一些文章，欢迎大家持续关注本公众号，及时获得下文知识。

如果觉得这篇文章对你有所帮助，还请帮忙点赞、在看、转发一下，非常感谢！

往期热门文章推荐